from pathlib import Path
import pandas as pd

this_dir = Path(__file__).resolve().parent
print(this_dir)
parts = []
for path in (this_dir / 'sales_data').rglob('*.xls*'):
    print(f'Reading {path.name}')
    part = pd.read_excel(path, index_col='transaction_id')
    parts.append(part)
df = pd.concat(parts)
pivot = pd.pivot_table(df, index='transaction_date', columns='store', values='amount', aggfunc='sum')
summary = pivot.resample('ME').sum()
summary.index.name = 'Month'

summary.to_excel(this_dir / 'sales_report_pandas.xlsx')
